package h2demo;


import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.h2.jdbcx.JdbcDataSource;
import org.h2.command.ddl.CreateTable;
import org.h2.engine.User;
import org.h2.util.New;
import java.sql.Connection;
import java.sql.Statement;

public class UtilsTest {
      private static DataSource getDataSource() {
		 JdbcDataSource ds = new JdbcDataSource();
		 ds.setURL("jbdc:h2:~/h2demo2");
		 return ds;		
		 }
       private static void createTable() throws SQLException{
	    	   String sql = "create table if not exists user("
						+"id int not null auto_increment primary key,"
						+"username varchar(128) uique not null,"
						+"password varchar(128) not null,"
						+"filename varchar(128) not null,"
						+"sex char(1) not null,"
						+"intersts varchar(128))";
	    	   new QueryRunner(getDataSource().execute(sql));
	    	   
       }
       private static void insertData() throws SQLException {
    	   		String drop = "delete from user";
    	   		String sql1 = "insert into user(username,password,filename,sex,intersts) "
    	   				+"values('tom','1234','a.png','M','books')";
    	   		String sql2 = "insert into user(username,password,filename,sex,intersts) "
    	   				+"values('mike','2344','b.png','M','balls')"; 
    	   		QueryRunner runner = new QueryRunner(getDataSource());
    	   		runner.execute(drop);
    	   		runner.execute(sql1);
    	   		runner.execute(sql2);
	}
       private static void selsct() {
    	   		String sql = "select username,password,filename,sex,intersts from user";
    	   		QueryRunner runner = new QueryRunner(getDataSource());
    	   		List<User> runner.query(sql, new BeanListHandler<>(User.class));
		    for(User user :users) {
		    	   System.out.println(user);
		    }
	}
       private static void main(String[] args) throws SQLException{
		// TODO Auto-generated method stub
    	   	createTable();
    	   	selsct();
    	   	insertData();
    	   	
	}
      
}
